﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using UpLoadDataOnTime.DataBaseUtil;
using UpLoadDataOnTime.Entity;

namespace UpLoadDataOnTime.Model
{
    class GetMZData
    {

        IDatabase db = DbFactory.CreateDb();

        //读取配置文件中的连接字符串（需添加引用System.Configuration）
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        private static string connStrP = ConfigurationManager.ConnectionStrings["dbConnStrP"].ConnectionString;
        private static string connStrL = ConfigurationManager.ConnectionStrings["dbConnStrL"].ConnectionString;

        //门诊患者基本信息
        public List<MZPatientInfo> getMZPatientInfo(string BeginDate,string EndDate)
        {
            string sql = "SELECT  '43120477700' AS YLJGDM," +
                         " '09' AS GRBSLX," +
                         " '43120477700' + '_' + PatientID AS GRBSH," +
                         " '01' AS ZJLX," +
                         " CASE WHEN PersonID = '' THEN '000000000000000000' " +
                         " ELSE ISNULL(PersonID,'000000000000000000') " +
                         " END AS ZJHM," +
                         " '云南省第三人民医院' AS YYMC," +
                         " CASE  WHEN Sex = '男' THEN '1'" +
                         " WHEN Sex = '女' THEN '2'" +
                         " END AS XB," +
                         " PatientName AS XM," +
                         " '90' AS HYZK," +
                         " CONVERT(VARCHAR(10), BirthDay, 120) AS CSRQ," +
                         " '530111000000' AS CSD, " +
                         " CASE Address WHEN '' THEN '未上传' ELSE Address END AS CSDMC," +
                         " '01' AS MZ," +
                         " '156' AS GJ," +
                         " CONVERT(VARCHAR(24), Tel) AS SJHM" +
                         " FROM dbo.PatientBase(NOLOCK)" +
                         " WHERE CreateDate >= @BeginDate AND CreateDate < @EndDate" +
                         " ORDER BY CreateDate";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            List<MZPatientInfo> list = db.Context.QueryMany<MZPatientInfo>(connStr, sql, parameters);
            return list;
        }

        //门诊挂号信息
        public List<MZTbResistration> GetMZTbResistrations(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                    " +
                         "       MZNum AS GHJLID,                                            " +
                         "       '云南省第三人民医院' AS YYMC,                                " +
                         "       '09' AS GRBSLX,                                             " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,                   " +
                         "       FPNum AS JZLSH,                                             " +
                         "       CASE WHEN XMAttrib = 1 THEN '0'                             " +
                         "            WHEN XMAttrib = 2 THEN '1'                             " +
                         "            WHEN XMAttrib = 3 THEN '0'                             " +
                         "       ELSE '0'                                                    " +
                         "       END AS SFJZ,                                                " +
                         "       CASE WHEN DelDate IS NULL THEN '01'                         " +
                         "       ELSE '02'                                                   " +
                         "       END AS GTHBZ,                                               " +
                         "       CONVERT(VARCHAR(10),RegisterDate,121) AS GTHSJ,             " +
                         "       CASE WHEN XMAttrib = 1 THEN '100'                           " +
                         "            WHEN XMAttrib = 2 THEN '200'                           " +
                         "            WHEN XMAttrib = 3 THEN '108'                           " +
                         "       ELSE '999'                                                  " +
                         "       END AS  GHLB,                                               " +
                         "       CASE WHEN ReserveFlag = 1 THEN '02'                         " +
                         "       ELSE '01'                                                   " +
                         "       END AS GHTJBM,                                              " +
                         "       CASE WHEN TreatmentCode = '1' THEN '01'                     " +
                         "            WHEN TreatmentCode = '2' THEN '02'                     " +
                         "            WHEN TreatmentCode = '3' THEN '03'                     " +
                         "            WHEN TreatmentCode = '4' THEN '04'                     " +
                         "            WHEN TreatmentCode = '5' THEN '05'                     " +
                         "            WHEN TreatmentCode = '6' THEN '06'                     " +
                         "            WHEN TreatmentCode = '7' THEN '07'                     " +
                         "            WHEN TreatmentCode = '8' THEN '08'                     " +
                         "            WHEN TreatmentCode = '9' THEN '99'                     " +
                         "       ELSE '99'                                                   " +
                         "       END AS BXLX,                                                " +
                         "       KSCode AS KSBM,                                             " +
                         "       KSName AS KSMC,                                             " +
                         "       CASE WHEN YSCode = '' THEN '未上传'                           " +
                         "       ELSE YSCode                                                 " +
                         "       END AS GHYSBH,                                              " +
                         "       CASE WHEN YSName = '' THEN '未上传'                           " +
                         "       ELSE YSName                                                 " +
                         "       END AS GHYSXM,                                              " +
                         "       '5' AS WDBZ,                                                " +
                         "       XMMoney AS GHZFY                                            " +
                         "FROM MZRegisterSheet(NOLOCK)                                       " +
                         "WHERE RegisterDate >= @BeginDate AND RegisterDate < @EndDate " +
                         "ORDER BY RegisterDate";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            Console.WriteLine(db.Context.QueryMany<MZTbResistration>(connStr, sql, parameters));
            return db.Context.QueryMany<MZTbResistration>(connStr, sql, parameters);
        }

        //门诊就诊信息
        public List<MZTbClinicRecord> GetMZTbClinicRecords(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                                   " +
                         "       CONVERT(VARCHAR(36),b.KeyNo) AS JZMXID,                                    " +
                         "       '09' AS GRBSLX,                                                            " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,                                  " +
                         "       '云南省第三人民医院' AS YYMC,                                               " +
                         "       a.MZnum AS JZLSH,                                                          " +
                         "       CASE WHEN RepeatFlag IS NULL THEN '0'                                      " +
                         "            WHEN RepeatFlag = 1 THEN '1'                                          " +
                         "       END AS SFFZ,                                                               " +
                         "       '01' AS LCYXLXDM,                                                           " +
                         "       CASE WHEN TreatmentCode = '1' THEN '01'                                    " +
                         "            WHEN TreatmentCode = '2' THEN '02'                                    " +
                         "            WHEN TreatmentCode = '3' THEN '03'                                    " +
                         "            WHEN TreatmentCode = '4' THEN '04'                                    " +
                         "            WHEN TreatmentCode = '5' THEN '05'                                    " +
                         "            WHEN TreatmentCode = '6' THEN '06'                                    " +
                         "            WHEN TreatmentCode = '7' THEN '07'                                    " +
                         "            WHEN TreatmentCode = '8' THEN '08'                                    " +
                         "            WHEN TreatmentCode = '9' THEN '99'                                    " +
                         "       ELSE '99'                                                                  " +
                         "       END AS HZSX,                                                               " +
                         "       CASE WHEN XMAttrib = 1 THEN '100'                                          " +
                         "            WHEN XMAttrib = 2 THEN '200'                                          " +
                         "            WHEN XMAttrib = 3 THEN '108'                                          " +
                         "       ELSE '999'                                                                 " +
                         "       END AS JZLX,                                                               " +
                         "       KSCode AS JZKSBM,                                                          " +
                         "       KSName AS JZKSMC,                                                          " +
                         "       ISNULL(CONVERT(VARCHAR(10),ProcessDate,121),'0000-00-00') AS JZKSRQ,       " +
                         "       CASE YSCode WHEN '' THEN '未上传' ELSE a.YSCode END AS ZZYSBH,                  " +
                         "       CASE YSName WHEN '' THEN '未上传' ELSE a.YSName END AS ZZYSXM,                  " +
                         "       CASE DiagCode WHEN '' THEN '未上传' ELSE b.DiagCode END AS MZZDDM,              " +
                         "       Diagnose AS MZZDMC,                                                        " +
                         "       CASE a.Advice WHEN '' THEN '未上传' ELSE a.Advice END AS JZZDSM,                " +
                         "       CASE Complaint WHEN '' THEN '未上传' ELSE ISNULL(Complaint,'未上传') END AS ZS     " +
                         "FROM MZRegisterSheet a(NOLOCK) JOIN dbo.MZDiagnose b(NOLOCK) ON a.MZNum = b.MZNum " +
                         "WHERE a.RegisterDate >= @BeginDate AND a.RegisterDate < @EndDate            " +
                         "ORDER BY a.RegisterDate ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbClinicRecord>(connStr, sql, parameters);
        }

        //门诊处方信息
        public List<MZTbRecipe> GetMZTbRecipes(string BeginDate,string EndDate)
        {
            string sql = "SELECT  '43120477700' AS YLJGDM ,                                                                                     " +
                         "            CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.CFNUM) + 'ZS' AS CFZID , " +
                         "            '云南省第三人民医院' AS YYMC,                                                                              " +
                         "            b.MZNUM AS JZLSH ,                                                                                        " +
                         "            '09' AS GRBSLX ,                                                                                          " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,                                                                      " +
                         "			b.CFNUM AS CFHM ,                                                                                           " +
                         "            CASE WHEN b.CFFLAG = 1 THEN '0101'                                                                        " +
                         "                 WHEN b.CFFLAG = 2 THEN '0103'                                                                        " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFDL ,                                                                                             " +
                         "            CASE WHEN CFTYPE = NULL THEN '01'                                                                         " +
                         "                 WHEN CFTYPE = 1 THEN '02'                                                                            " +
                         "                 WHEN CFTYPE = 2 THEN '03'                                                                            " +
                         "                 WHEN CFTYPE = 3 THEN '05'                                                                            " +
                         "                 WHEN CFTYPE = 4 THEN '04'                                                                            " +
                         "                 WHEN CFTYPE = 5 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 6 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 7 THEN '04'                                                                            " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFLX ,                                                                                             " +
                         "			  CONVERT(VARCHAR(10),CFDATE,121) AS KFSJ ,                                                                 " +
                         "            a.YSCODE AS KFYSBH ,                                                                                      " +
                         "            a.YSNAME AS KFYSXM ,                                                                                      " +
                         "            b.YSKSCODE AS KFKSDM ,                                                                                    " +
                         "            CONVERT(VARCHAR(76),b.YSKSNAME) AS KFKSMC ,                                                               " +
                         "            CASE ISNULL(DiagCode,'未上传') WHEN '' THEN '未上传' ELSE ISNULL(DiagCode,'未上传') END AS XYZDDM ,        " +
                         "            ISNULL(DiagName,'未上传') AS XYZDMC ,                                                                      " +
                         "            '未上传' AS ZYBMDM ,                                                                                           " +
                         "            '未上传' AS ZYBMMC ,                                                                                           " +
                         "            '未上传' AS ZYZHDM ,                                                                                           " +
                         "            '未上传' AS ZYZHMC ,                                                                                           " +
                         "            c.CFMONEY AS CFJE                                                                                         " +
                         "FROM dbo.MZCFYPK a(NOLOCK)                                                                                            " +
                         "         JOIN MZCFINF_YZ b(NOLOCK) ON b.FPNUM = a.FPNum                                                               " +
                         "         JOIN dbo.MZCFINF c(NOLOCK) ON c.CFNUM = a.CFNUM                                                              " +
                         "         LEFT JOIN dbo.MZDiagnose d(NOLOCK) ON d.MZNum = b.MZNum                                                      " +
                         "WHERE b.CFDATE >= @BeginDate AND b.CFDATE < @EndDate                                                                  " +
                         "UNION ALL                                                                                                             " +
                         "SELECT  '43120477700' AS YLJGDM ,                                                                                     " +
                         "            CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.CFNUM) + 'ZS' AS CFZID , " +
                         "            '云南省第三人民医院' AS YYMC,                                                                              " + 
                         "            b.MZNUM AS JZLSH ,                                                                                        " +
                         "            '09' AS GRBSLX ,                                                                                          " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,                                                                      " +
                         "			  b.CFNUM AS CFHM ,                                                                                         " +
                         "            CASE WHEN b.CFFLAG = 1 THEN '0101'                                                                        " +
                         "                 WHEN b.CFFLAG = 2 THEN '0103'                                                                        " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFDL ,                                                                                             " +
                         "            CASE WHEN CFTYPE = NULL THEN '01'                                                                         " +
                         "                 WHEN CFTYPE = 1 THEN '02'                                                                            " +
                         "                 WHEN CFTYPE = 2 THEN '03'                                                                            " +
                         "                 WHEN CFTYPE = 3 THEN '05'                                                                            " +
                         "                 WHEN CFTYPE = 4 THEN '04'                                                                            " +
                         "                 WHEN CFTYPE = 5 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 6 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 7 THEN '04'                                                                            " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFLX ,                                                                                             " +
                         "			  CONVERT(VARCHAR(10),CFDATE,121) AS KFSJ ,                                                                 " +
                         "            a.YSCODE AS KFYSBH ,                                                                                      " +
                         "            a.YSNAME AS KFYSXM ,                                                                                      " +
                         "            b.YSKSCODE AS KFKSDM ,                                                                                    " +
                         "            CONVERT(VARCHAR(76),b.YSKSNAME) AS KFKSMC ,                                                               " +
                         "            CASE ISNULL(DiagCode,'未上传') WHEN '' THEN '未上传' ELSE ISNULL(DiagCode,'未上传') END AS XYZDDM ,                      " +
                         "            ISNULL(DiagName,'未上传') AS XYZDMC ,                                                                          " +
                         "            '未上传' AS ZYBMDM ,                                                                                           " +
                         "            '未上传' AS ZYBMMC ,                                                                                           " +
                         "            '未上传' AS ZYZHDM ,                                                                                           " +
                         "            '未上传' AS ZYZHMC ,                                                                                           " +
                         "            c.CFMONEY AS CFJE                                                                                         " +
                         "FROM dbo.MZCFYPKHIS a(NOLOCK)                                                                                         " +
                         "         JOIN MZCFINF_YZ b(NOLOCK) ON b.FPNUM = a.FPNum                                                               " +
                         "         JOIN dbo.MZCFINFHIS c(NOLOCK) ON c.CFNUM = a.CFNUM                                                           " +
                         "         LEFT JOIN dbo.MZDiagnose d(NOLOCK) ON d.MZNum = b.MZNum                                                      " +
                         "WHERE b.CFDATE >= @BeginDate AND b.CFDATE < @EndDate                                                                  ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbRecipe>(connStr, sql, parameters);
        }

        //门诊处方药品信息 
        public List<MZTbMedicine> GetMZTbMedicines(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                                                                                                               " +
"                                CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.KEYNO) + 'ZS' AS CFMXID,                                              " +
"                                '云南省第三人民医院' AS YYMC,                                                                                                                           " +
"                                MZNUM AS JZLSH,                                                                                                                                        " +
"                                '09' AS GRBSLX,                                                                                                                                        " +
"                                '43120477700' + '_' + PatientID AS GRBSH,                                                                                                              " +
"                                a.CFNUM AS CFZID,                                                                                                                                      " +
"                                a.YZID AS CFYZZH,                                                                                                                                      " +
"                                a.KMCODE AS ZLXMLBBM,                                                                                                                                  " +
"                                c.XMFLAG AS XMFLBM,                                                                                                                                    " +
"                                a.KMNAME AS XMFLMC,                                                                                                                                    " +
"                                '99' AS JXDM,                                                                                                                                          " +
"                                a.GOODSNAME AS YPGG,                                                                                                                                   " +
"                                CASE a.YPPath  WHEN '外阴道给药' THEN '605'                                                                                                            " +
"											   WHEN '皮试' THEN '401'                                                                                                                   " +
"											   WHEN '塞肛' THEN '2'                                                                                                                     " +
"											   WHEN '喷雾' THEN '609'                                                                                                                   " +
"											   WHEN '清洗' THEN '9'                                                                                                                     " +
"											   WHEN '吸入' THEN '606'                                                                                                                   " +
"											   WHEN '漱口' THEN '9'                                                                                                                     " +
"											   WHEN '滴鼻' THEN '608'                                                                                                                   " +
"											   WHEN '舌下含服' THEN '3'                                                                                                                 " +
"											   WHEN '雾化吸入' THEN '5'                                                                                                                 " +
"											   WHEN '其他' THEN '9'                                                                                                                     " +
"											   WHEN NULL THEN '9'                                                                                                                       " +
"											   WHEN '外涂' THEN '612'                                                                                                                   " +
"											   WHEN '滴耳' THEN '699'                                                                                                                   " +
"											   WHEN '口服' THEN '1'                                                                                                                     " +
"											   WHEN '涂眼' THEN '699'                                                                                                                   " +
"											   WHEN '静推' THEN '404'                                                                                                                   " +
"											   WHEN '局麻' THEN '699'                                                                                                                   " +
"											   WHEN '静滴' THEN '404'                                                                                                                   " +
"											   WHEN '滴眼' THEN '607'                                                                                                                   " +
"											   WHEN '灌肠用' THEN '604'                                                                                                                 " +
"											   WHEN '皮下注射' THEN '401'                                                                                                               " +
"											   WHEN '肌肉注射' THEN '403'                                                                                                               " +
"											   WHEN '外敷' THEN '611'                                                                                                                   " +
"											   ELSE '9'                                                                                                                                 " +
"											   END AS YYTJDM,                                                                                                                           " +
"                                CASE WHEN YZUSEDMETHOD = 'bid' THEN '01'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'biw' THEN '02'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Hs'  THEN '03'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q12h' THEN '04'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'q1h' THEN '05'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q3h' THEN '06'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q6h' THEN '07'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q8h' THEN '08'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qd' THEN '09'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'qid' THEN '10'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qod' THEN '11'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qw' THEN '12'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'st' THEN '13'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'TID' THEN '14'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30D' THEN '15'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2W' THEN '16'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'TIW' THEN '17'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30M' THEN '18'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2H' THEN '19'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q4H' THEN '20'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5H' THEN '21'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q12H' THEN '22'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q72H' THEN '23'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'QM' THEN '24'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QN' THEN '25'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QON' THEN '26'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5D' THEN '27'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q10D' THEN '28'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C12H' THEN '29'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C24H' THEN '30'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'PRN' THEN '31'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'AC' THEN '32'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'AM' THEN '33'                                                                                                                " +
"                                     ELSE '99'                                                                                                                                         " +
"                                END AS SYPCDM,                                                                                                                                         " +
"                                ISNULL(YZUSEDMETHOD,'未上传') AS YYPC,                                                                                                                 " +
"                                YPJL AS SYCJL,                                                                                                                                         " +
"                                ISNULL(CONVERT(VARCHAR(6),YPJLUNIT),'未上传') AS SYJLDW,                                                                                                " +
"                                '0.00' AS SYZJL ,                                                                                                                                      " +
"                                '未上传' AS ZYYYFF,                                                                                                                                         " +
"                                a.CFCOUNT AS FYSL,                                                                                                                                     " +
"                                a.UNITNAME AS FYSLDW,                                                                                                                                  " +
"                                '0.00' AS YYTS,                                                                                                                                        " +
"                         	     '未上传' AS YWLX,                                                                                                                                           " +
"                                CONVERT(VARCHAR(10),a.FYDATE,120) AS CFKSSJ,                                                                                                           " +
"                                '0000-00-00' AS CFTZSJ                                                                                                                                 " +
"                         FROM MZCFYPK a(NOLOCK)                                                                                                                                        " +
"                              JOIN dbo.MZCFINF b(NOLOCK) ON a.CFNUM = b.CFNUM                                                                                                          " +
"                              JOIN dbo.MZCFYPK_YZ c(NOLOCK) ON a.CFNUM = c.CFNUM                                                                                                       " +
"                         WHERE  a.FPDATE >= @BeginDate AND a.FPDATE < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbMedicine>(connStr, sql, parameters);
        }

        //门诊手术信息
        public List<MZTbOperation> GetMZTbOperations(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                         " +
                         "       a.KEYNO AS SSMXLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                      " +
                         "       b.MZNUM AS JZLSH,                                 " +
                         "       '09' AS GRBSLX,                                   " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,         " +
                         "       '1' AS  MZZYBZ,                                   " +
                         "       '9' AS SSLX,                                      " +
                         "CASE c.DiagCode WHEN '' THEN '未上传' ELSE c.DiagCode END AS SSCZDM, " +
                         "       CHECKNAME AS SSCZMC,                              " +
                         "       c.DiagName AS SSQZD,                              " +
                         "       c.DiagName AS SSHZD,                              " +
                         "       CONVERT(VARCHAR(10),a.FPDATE,121) AS SSKSSJ,      " +
                         "       CONVERT(varchar(10),GETDATE(),120) AS SSJSSJ,     " +
                         "       a.YSCODE AS SSYSBH,                               " +
                         "       a.YSNAME AS SSYSXM,                               " +
                         "       '4' AS QKYHDJ                                     " +
                         "FROM dbo.MZCHECK a(NOLOCK)                               " +
                         "JOIN dbo.MZINVOICE b(NOLOCK)                             " +
                         "ON b.FPNum = a.FPNUM                                     " +
                         "JOIN dbo.MZDiagnose c(NOLOCK)                            " +
                         "ON c.MZNum = b.MZNum                                     " +
                         "WHERE KMCODE = 'G' AND a.DELDATE IS NULL                 " +
                         "AND a.FPDATE >= @BeginDate AND a.FPDATE < @EndDate       " +
                         "AND b.CDNUM IS NULL                                      " +
                         "UNION ALL                                                " +
                         "SELECT '43120477700' AS YLJGDM ,                         " +
                         "       a.KEYNO AS SSMXLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                      " +
                         "       b.MZNUM AS JZLSH,                                 " +
                         "       '09' AS GRBSLX,                                   " +
                         "       '43120477700' + '_' + PatientID AS GRBSH,         " +
                         "       '1' AS  MZZYBZ,                                   " +
                         "       '9' AS SSLX,                                      " +
                         "CASE c.DiagCode WHEN '' THEN '未上传' ELSE c.DiagCode END AS SSCZDM, " +
                         "       CHECKNAME AS SSCZMC,                              " +
                         "       c.DiagName AS SSQZD,                              " +
                         "       c.DiagName AS SSHZD,                              " +
                         "       CONVERT(VARCHAR(10),a.FPDATE,121) AS SSKSSJ,      " +
                         "       CONVERT(varchar(10),GETDATE(),120) AS SSJSSJ,     " +
                         "       a.YSCODE AS SSYSBH,                               " +
                         "       a.YSNAME AS SSYSXM,                               " +
                         "       '4' AS QKYHDJ                                     " +
                         "FROM dbo.MZCHECKHIS a(NOLOCK)                            " +
                         "JOIN dbo.MZINVOICEHIS b(NOLOCK)                          " +
                         "ON b.FPNum = a.FPNUM                                     " +
                         "JOIN dbo.MZDiagnose c(NOLOCK)                            " +
                         "ON c.MZNum = b.MZNum                                     " +
                         "WHERE KMCODE = 'G' AND a.DELDATE IS NULL                 " +
                         "AND a.FPDATE >= @BeginDate AND a.FPDATE < @EndDate       " +
                         "AND b.CDNUM IS NULL ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbOperation>(connStr, sql, parameters);
        }

        //门诊检查信息
        public List<MZTbCheckRecord> GetMZTbCheckRecords(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                              " +
                         "       '云南省第三人民医院' AS YYMC,                          " +
                         "       AccessionNumber AS JCBGID,                            " +
                         "       OutPatientNum AS JZLSH,                               " +
                         "       '09' AS GRBSLX,                                       " +
                         "       '43120477700' + '_' + PatientID_His AS GRBSH,         " +
                         "       '1' AS MZZYBZ,                                        " +
                         "       ISNULL(CONVERT(VARCHAR(20),PatientStudyDate,120),'未上传') AS JCRQ,    " +
                         "       ReportDoctor AS JCYSQM,                               " +
                         "       ClinicName AS BGJGMC,                                 " +
                         "       ClinicName AS BGKSMC,                                 " +
                         "       ReportDoctor AS BGYSQM,                               " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGKGSJ,         " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGZGTS,         " +
                         "       CASE ModalityType WHEN 'DR' THEN '04'                 " +
                         "                         WHEN 'CT' THEN '01'                 " +
                         "                         WHEN 'B超' THEN '06'                " +
                         "                         WHEN '1胃肠镜' THEN '08'            " +
                         "                         WHEN '5胃肠镜' THEN '08'            " +
                         "                         WHEN 'B超-JR' THEN '06'             " +
                         "                         WHEN 'MG' THEN '10'                 " +
                         "                         WHEN 'MR' THEN '02'                 " +
                         "                         WHEN 'OT' THEN '10'                 " +
                         "                         WHEN 'US' THEN '06'                 " +
                         "                         WHEN '病理' THEN '07'               " +
                         "                         WHEN '肠镜' THEN '08'               " +
                         "                         WHEN '电子气管镜' THEN '08'         " +
                         "                         WHEN '胃肠镜' THEN '08'             " +
                         "       ELSE '10'                                             " +
                         "       END AS JCLX,                                          " +
                         "       Modality AS YQBM,                                     " +
                         "       ClinicName AS JCKSMC,                                 " +
                         "       CASE ScanWay WHEN '' THEN '未上传' ELSE ScanWay END AS JCBW, " +
                         "       BodyPartExamined AS JCMC,                             " +
                         "       CASE WHEN Abnormality IS NULL THEN 2                  " +
                         "            WHEN Abnormality = 2 THEN 2                      " +
                         "            WHEN Abnormality = 3 THEN 3                      " +
                         "       ELSE Abnormality                                      " +
                         "       END AS YYS,                                           " +
                         "       DiagnosesText AS BGLCZD,                              " +
                         "       DiagnosesText AS YXBX,                                " +
                         "       DiagnosesText AS YXZD,                                " +
                         "       '1' AS SFYYY                                          " +
                         "FROM PatientSchedule a(NOLOCK)                               " +
                         "JOIN dbo.PatientBase b(NOLOCK) ON b.PatientID = a.PatientID  " +
                         "WHERE DelDate IS NULL                                        " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND OutPatientNum IS NOT NULL                                " +
                         "AND OutPatientNum <> ''                                      " +
                         "AND ReportDate >= @BeginDate AND ReportDate < @EndDate       ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbCheckRecord>(connStrP, sql, parameters);
        }

        //门诊检验信息
        public List<MZTbAssayRecord> GetMZTbAssayRecords(string BeginDate,string EndDate)
        {
            string sql = @"select '43120477700' as YLJGDM,                                                                                                                    
                                CONVERT(char(8),a.date_test,112) + a.YBH as JYBGID,                                                                                           
                                '云南省第三人民医院' AS YYMC,                                                                                                                    
                                a.Patient_Code as JZLSH,                                                                                                                      
                                '09' as GRBSLX,                                                                                                                               
                                '43120477700' + '_' + a.Patient_Code as GRBSH,                                                                                                
                                CASE a.patient_kind WHEN '1' THEN a.patient_kind                                                                                              
                                                    WHEN '2' THEN '1'                                                                                                         
                                                    WHEN '0' THEN '2'                                                                                                         
                                                    WHEN '8' THEN '2'                                                                                                         
                                ELSE '9'                                                                                                                                      
                                END as MZZYBZ,                                                                                                                                
                                (case a.Patient_Kind  when '1' then a.Patient_Code when '2' then a.Patient_Code else NULL end) as MJZH,                                       
                                (case a.Patient_Kind  when '0' then a.Patient_Code when '8' then a.Patient_Code else NULL end)  as ZYH,                                       
                                CASE ISNULL(a.patient_kind,NULL) WHEN '1' THEN a.patient_kind                                                                                 
                                                               WHEN '2' THEN a.patient_kind                                                                                   
                                                               WHEN '0' THEN '3'                                                                                              
                                                               WHEN '8' THEN '3'                                                                                              
                                ELSE '4'                                                                                                                                      
                                END as HZLXDM,                                                                                                                                
                                A.YBH AS JYBBH,                                                                                                                               
                                d.Name as JYXMMC,                                                                                                                             
                                (select name from ut_dict_user where User_ID = A.reporter) as JYYSQM,                                                                         
                                CONVERT(VARCHAR(10),A.Date_Check,120) AS JYRQ,                                                                                                
                                ISNULL((select Group_Name from UT_DICT_Group  where Group_Code =(select check_group from ut_dict_report  where code = LEFT(A.YBH,2))),'未上传') AS BGKSMC, 
                                '未上传' AS JYBGJG,                                                                                                                                  
                                 CASE A.Kind WHEN '+7' THEN '0032'
                                             WHEN '1' THEN '0002'
                                             WHEN '10' THEN '0043'
                                             WHEN '11' THEN '0051'
                                             WHEN '12' THEN '0048'
                                             WHEN '13' THEN '0047'
                                             WHEN '14' THEN '0041'
                                             WHEN '15' THEN '0054'
                                             WHEN '17' THEN '0039'
                                             WHEN '18' THEN '0035'
                                             WHEN '19' THEN '0046'
                                             WHEN '2' THEN '0004'
                                             WHEN '20' THEN '0024'
                                             WHEN '22' THEN '0051'
                                             WHEN '23' THEN '0044'
                                             WHEN '26' THEN '0034'
                                             WHEN '29' THEN '0051'
                                             WHEN '3' THEN '0001'
                                             WHEN '30' THEN '0037'
                                             WHEN '31' THEN '0007'
                                             WHEN '32' THEN '0002'
                                             WHEN '33' THEN '0037'
                                             WHEN '34' THEN '0025'
                                             WHEN '35' THEN '9999'
                                             WHEN '36' THEN '0023'
                                             WHEN '4' THEN '0003'
                                             WHEN '40' THEN '0054'
                                             WHEN '5' THEN '0021'
                                             WHEN '54' THEN '0051'
                                             WHEN '56' THEN '0051'
                                             WHEN '6' THEN '0030'
                                             WHEN '61' THEN '0031'
                                             WHEN '62' THEN '0050'
                                             WHEN '64' THEN '0021'
                                             WHEN '7' THEN '0031'
                                             WHEN '73' THEN '0050'
                                             WHEN '75' THEN '0054'
                                             WHEN '76' THEN '0021'
                                             WHEN '8' THEN '0036'
                                             WHEN '95' THEN '0049'
                                             ELSE '9999'
                                             END AS BBDM, 
                                (select Kind_Name from UT_Check_Module_Kind where Kind_Code = a.kind ) AS BBMC,                                                                  
                                ISNULL((select check_group from ut_dict_report  where code = LEFT(A.YBH,2)),'9999') AS BGDLBBM,                                                  
                                 (select report_name from ut_dict_report where code = LEFT(A.YBH,2)) AS BGDLB                                                                    
                         from   ut_check_patient a(NOLOCK) left outer join ut_check_collection c (nolock) on a.date_test = c.date_test and a.ybh = c.ybh, UT_DICT_Project_Kind d 
                         WHERE  a.Flag_Send ='5' and c.Item_Check = d.Code                                                                                                       
                         AND a.Collection_time >= @BeginDate AND a.Collection_time < @EndDate                                                                                    
                         AND a.patient_kind IN ('1','2')                                                                                                                    ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbAssayRecord>(connStrL, sql, parameters);
        }

        //门诊检验结果指标信息
        public List<MZTbAssayQuotaResult> GetMZTbAssayQuotaResults(string BeginDate,string EndDate)
        {
            string sql = "SELECT     '43120477700' as YLJGDM,                                                                                                                                                                    " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYZBLSH,                                                                                                                           " +
                         "           '云南省第三人民医院' AS YYMC,                                                                                                                                                    " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYBGID,                                                                                                                            " +
                         "           convert(VARCHAR(10),r.test_date,120) as BGRQ,                                                                                                                                  " +
                         "           (select name from dbo.ut_dict_user where user_id = p.reporter) as JCRXM,                                                                                                       " +
                         "           r.item_code as YBSFDM,                                                                                                                                                         " +
                         "           'ZZZZZZZZZZZZZZZ' AS YZMXBMYB,                                                                                                                                                 " +
                         "CASE(select test_mothod_name from ut_dict_method where test_mothod in (select test_method from ut_dict_items where item_code = r.item_code)) WHEN '' THEN '未上传'                         " +
                         "           ELSE ISNULL((select test_mothod_name from ut_dict_method where test_mothod in (select test_method from ut_dict_items where item_code= r.item_code)),'未上传') END AS JCFFMC,    " +
                         "CASE(select Item_Name from dbo.UT_DICT_Items where Item_Code = r.Item_Code) WHEN '' THEN '未上传'                                                                                          " +
                         "           ELSE ISNULL((select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code),'未上传') END AS JCZBMC,                                                                      " +
                         "            case (select Value_Type from dbo.UT_DICT_Items where Item_Code = r.Item_Code) when '1' then '1' when '2' then '2' when '3' then '2' when '4' then '2' else '9' end as ZBJGLX, " +
                         "           r.Test_Value2 as JCJGZB,                                                                                                                                                       " +
                         "           dbo.fun_share_midat(r.Text_range,1,'-') as CKSX,                                                                                                                               " +
                         "           dbo.fun_share_midat(r.Text_range,0,'-') as CKXX,                                                                                                                               " +
                         "           r.Test_Value2 AS DXCKJG,                                                                                                                                                        " +
                         "           '项' as JLDW                                                                                                                                                                    " +
                         "FROM       dbo.UT_Check_Result AS r,dbo.ut_check_patient as p                                                                                                                             " +
                         "WHERE      p.Date_test = r.Test_Date and p.YBH = r.YBH and p.flag_send = '5'                                                                                                              " +
                         "AND r.Test_Date >= @BeginDate AND r.Test_Date < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            return db.Context.QueryMany<MZTbAssayQuotaResult>(connStrL, sql, parameters);
        }
    }
}
